package com.ztjava.core.tool;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.ztjava.core.bean.ColumnBean;
import com.ztjava.core.bean.Database;
import com.ztjava.core.bean.TableBean;

public class MysqlTool {
	private static Logger logger = Logger.getLogger(MysqlTool.class);
	/***
	 * 组建表格
	 * 
	 * @param dataSource
	 * @param dbname
	 * @param tableBean
	 */
	@SuppressWarnings("resource")
	public void bulderTable(Database dataBase, TableBean tableBean) {
		Connection conn = null;
		ResultSet rs = null;
		Statement stm = null;
		System.out.println(tableBean.getTableName());
		try {
			conn = dataBase.getDataSource().getConnection();
			stm = conn.createStatement();
			rs = stm.executeQuery("select * from information_schema.TABLES where TABLE_SCHEMA='"
					+ dataBase.getDbName()
					+ "' and TABLE_NAME='"
					+ tableBean.getTableName() + "'");
			if (!rs.next()) {
				addTable(tableBean.getTableName(),tableBean.getCharacter(), tableBean.getColumnMap(),
						stm);
			} else {
				rs = stm.executeQuery("select COLUMN_NAME,COLUMN_TYPE from information_schema.COLUMNS where TABLE_SCHEMA='"
						+ dataBase.getDbName()
						+ "' and TABLE_NAME='"
						+ tableBean.getTableName()
						+ "' AND COLUMN_NAME not in ('id','create_time','update_time','create_user','update_user','enable_flag')");
				List<ColumnBean> dbColumnList = new ArrayList<ColumnBean>();
				while (rs.next()) {
					ColumnBean item = new ColumnBean();
					item.setName(rs.getString("COLUMN_NAME"));
					item.setType(rs.getString("COLUMN_TYPE"));
					dbColumnList.add(item);
				}
				alertTable(tableBean.getTableName(), tableBean.getColumnMap(),stm, dbColumnList);
			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(tableBean.getTableName()+"数据表构建失败，请检查建表数组！");
			throw new RuntimeException();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stm != null)
					stm.close();
			} catch (Exception e) {
			}
		}
	}

	/***
	 * 创建表
	 * 
	 * @param tableName
	 * @param columnMap
	 * @param stm
	 */
	public void addTable(String tableName,String character, Map<String, ColumnBean> columnMap,Statement stm) {
		StringBuilder sql = new StringBuilder();
		sql.append("CREATE TABLE `" + tableName + "` (");
		sql.append("`id` int(11) NOT NULL AUTO_INCREMENT,");
		ColumnBean column = null;
		for (String columnName : columnMap.keySet()) {
			column = columnMap.get(columnName);
			sql.append("`" + columnName + "` " + column.getType());
			if (column.getDefaultValue() != null) {
				if (column.getType().toLowerCase().indexOf("varchar") > -1) {
					sql.append(" DEFAULT '" + column.getDefaultValue() + "'");
				} else {
					sql.append(" DEFAULT " + column.getDefaultValue());
				}
			}
			sql.append(",");
		}
		sql.append("`enable_flag` varchar(1) DEFAULT 'T',");
		sql.append("`update_user` int(11),");
		sql.append("`update_time` datetime,");
		sql.append("`create_user` int(11),");
		sql.append("`create_time` timestamp DEFAULT CURRENT_TIMESTAMP,");
		sql.append("PRIMARY KEY (`id`)");
		sql.append(") ENGINE = INNODB DEFAULT CHARSET="+character);
		try {
			stm.execute(sql.toString());
			logger.warn(tableName + "创建完毕！");
		} catch (Exception e) {
			logger.error(tableName+"创建失败！");
			logger.error(tableName+":"+sql.toString());
			e.printStackTrace();
		}

	}

	/***
	 * 变更表
	 * 
	 * @param tableName
	 * @param columnMap
	 * @param stm
	 * @param dbColumnList
	 */
	public void alertTable(String tableName, Map<String, ColumnBean> columnMap,
			Statement stm, List<ColumnBean> dbColumnList) {
		List<ColumnBean> addList = new ArrayList<ColumnBean>();
		List<ColumnBean> dropList = new ArrayList<ColumnBean>();
		List<ColumnBean> modifyList = new ArrayList<ColumnBean>();
		for (String columnName : columnMap.keySet()) {
			boolean is_have = false;
			for (int j = 0; j < dbColumnList.size(); j++) {
				if (columnName.toLowerCase().equals(dbColumnList.get(j).getName().toLowerCase())) {
					is_have = true;
					break;
				}
			}
			if (!is_have) {
				addList.add(columnMap.get(columnName));
			}
		}
		for (int i = 0; i < dbColumnList.size(); i++) {
			boolean is_have = false;
			boolean is_modify = false;
			for (String columnName : columnMap.keySet()) {
				if (dbColumnList.get(i).getName().toLowerCase()
						.equals(columnName.toLowerCase())) {
					is_have = true;
					if (!dbColumnList.get(i).getType().toLowerCase()
							.equals(columnMap.get(columnName).getType().toLowerCase())) {
						dbColumnList.get(i).setType(columnMap.get(columnName).getType());
						is_modify = true;
					}
					break;
				}
			}
			if (!is_have) {
				dropList.add(dbColumnList.get(i));
			} else if (is_modify) {
				// 如果已存在，并且需要修改
				modifyList.add(dbColumnList.get(i));
			}
		}

		alertDropTable(tableName, dropList, stm);
		alertAddTable(tableName, addList, stm);
		alertModifyTable(tableName, modifyList, stm);
	}

	/***
	 * 删除字段
	 * 
	 * @param tableName
	 * @param dropList
	 * @param stm
	 */
	public void alertDropTable(String tableName, List<ColumnBean> dropList,
			Statement stm) {
		if (dropList.size() == 0)
			return;
		StringBuilder sql = new StringBuilder();
		sql.append("ALTER TABLE `" + tableName + "`");
		for (int i = 0; i < dropList.size(); i++) {
			sql.append("DROP COLUMN `" + dropList.get(i).getName() + "`");
			if (i < (dropList.size() - 1)) {
				sql.append(",");
			}
		}
		try {
			stm.execute(sql.toString());
			logger.warn(tableName + "表结构DROP更新成功！");
		} catch (Exception e) {
			logger.error(tableName + "表结构DROP更新失败！");
			logger.error(tableName+":"+sql.toString());
			e.printStackTrace();
		}
	}

	/***
	 * 新增字段
	 * 
	 * @param tableName
	 * @param addList
	 * @param stm
	 */
	public void alertAddTable(String tableName, List<ColumnBean> addList,
			Statement stm) {
		if (addList.size() == 0)
			return;
		StringBuilder sql = new StringBuilder();
		sql.append("ALTER TABLE `" + tableName + "`");
		for (int i = 0; i < addList.size(); i++) {
			sql.append("ADD COLUMN `" + addList.get(i).getName() + "` "
					+ addList.get(i).getType());
			if (addList.get(i).getDefaultValue() != null) {
				if (addList.get(i).getType().toLowerCase().indexOf("varchar") > -1) {
					sql.append(" DEFAULT '" + addList.get(i).getDefaultValue()
							+ "'");
				} else {
					sql.append(" NOT NULL DEFAULT "
							+ addList.get(i).getDefaultValue());
				}
			}
			if (i < (addList.size() - 1)) {
				sql.append(",");
			}
		}
		try {
			stm.execute(sql.toString());
			logger.warn(tableName + "表结构ADD更新成功！");
		} catch (Exception e) {
			logger.error(tableName + "表结构ADD更新失败！");
			logger.error(tableName+":"+sql.toString());
			e.printStackTrace();
		}
	}

	/***
	 * 修改字段
	 * 
	 * @param tableName
	 * @param modifyList
	 * @param stm
	 */
	public void alertModifyTable(String tableName, List<ColumnBean> modifyList,
			Statement stm) {
		if (modifyList.size() == 0)
			return;
		StringBuilder sql = new StringBuilder();
		sql.append("ALTER TABLE `" + tableName + "`");
		for (int i = 0; i < modifyList.size(); i++) {
			sql.append("MODIFY COLUMN `" + modifyList.get(i).getName() + "` "
					+ modifyList.get(i).getType());
			if (modifyList.get(i).getDefaultValue() != null) {
				if (modifyList.get(i).getType().toLowerCase()
						.indexOf("varchar") > -1) {
					sql.append(" DEFAULT '"
							+ modifyList.get(i).getDefaultValue() + "'");
				} else {
					sql.append(" NOT NULL DEFAULT "
							+ modifyList.get(i).getDefaultValue());
				}
			}
			if (i < (modifyList.size() - 1)) {
				sql.append(",");
			}
		}
		try {
			stm.execute(sql.toString());
			logger.warn(tableName + "表结构MODIFY更新成功！");
		} catch (Exception e) {
			logger.error(tableName + "表结构MODIFY更新失败！");
			logger.error(tableName+":"+sql.toString());
			e.printStackTrace();
		}
	}
}
